package com.junzhiit.nengji.dao.impl;

import java.util.Date;
import java.util.List;

import org.springframework.stereotype.Repository;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.bainaeco.framework.util.DateUtil;
import com.junzhiit.nengji.bo.OrdersGoodsBO;
import com.junzhiit.nengji.bo.RebateInfoBO;
import com.junzhiit.nengji.dao.OrdersGoodsDAO;
import com.junzhiit.nengji.model.OrdersGoods;
import com.junzhiit.nengji.model.enums.Is;
import com.junzhiit.nengji.model.enums.OrderGoodsStatus;
import com.junzhiit.nengji.model.enums.OrderStatus;

@Repository
public class OrdersGoodsDAOImpl extends AbstractBaseDAO<OrdersGoods> implements OrdersGoodsDAO {

	public List<OrdersGoodsBO> getOrderGoodsOrderId(String orderId) {
		StringBuilder sql = new StringBuilder();
		StatementParameter params = new StatementParameter();
		sql.append("select og.number,og.id,og.goods_name,og.goods_id,t.name as type_name,");
		sql.append("og.`status`,o.`status` as orders_status,og.file_name,og.is_refund_goods,og.is_refund_money,");
		sql.append("og.price,og.sku_content_name standard,og.hint,og.profit,");
		sql.append("g.is_open_rebate,gr.fail_reason 'goods_refund_reason',");
		sql.append("r.status 'orders_refund_status',gr.status 'goods_refund_status',r.fail_reason 'orders_refund_reason'");
		sql.append(" from orders_goods og");
		sql.append(" inner join goods g on g.id = og.goods_id");
		sql.append(" inner join orders o on o.id = og.order_id");
		sql.append(" inner join type t on t.id = og.type_id");
		sql.append(" left join orders_refund r on r.orders_goods_id = og.id");
		sql.append(" left join goods_refund gr on gr.orders_goods_id = og.id");
		sql.append(" where og.order_id = ? and og.is_delete = ?");
		params.setString(orderId);
		params.setInt(IsDelete.NO.toIntValue());
		return super.queryForList(sql.toString(), OrdersGoodsBO.class, params);
	}

	public int getTotalCount(String orderId) {
		StringBuilder sql = new StringBuilder();
		StatementParameter params = new StatementParameter();
		sql.append("select count(og.id) from orders_goods og");
		sql.append(" inner join goods g on g.id = og.goods_id");
		sql.append(" inner join orders o on o.id = og.order_id");
		sql.append(" inner join type t on t.id = og.type_id");
		sql.append(" left join orders_refund r on r.orders_goods_id = og.id");
		sql.append(" left join goods_refund gr on gr.orders_goods_id = og.id");
		sql.append(" where og.order_id = ? and og.is_delete = ?");
		params.setString(orderId);
		params.setInt(IsDelete.NO.toIntValue());
		return super.queryForInt(sql.toString(), params);
	}

	public List<OrdersGoodsBO> getOrderGoods(String orderId) {
		StringBuilder sql = new StringBuilder();
		StatementParameter params = new StatementParameter();
		sql.append("select og.id,og.file_name as cover_image,og.goods_code code,og.goods_name as name,");
		sql.append(" og.type,og.goods_id,o.shop_id,og.number,og.is_open_sku,o.pay_time,o.confirm_time,");
		sql.append(" og.is_open_member,og.sku_content_name standard,og.price,og.reference_price");
		sql.append(" from orders_goods og");
		sql.append(" inner join orders o on og.order_id=o.id");
		sql.append(" where og.order_id = ? and og.is_delete = ? ");
		params.setString(orderId);
		params.setInt(IsDelete.NO.toIntValue());
//		params.setInt(OrderGoodsStatus.BUY.toIntValue());
		sql.append(" order by og.created_time desc");
		return super.queryForList(sql.toString(), OrdersGoodsBO.class, params);
	}
	
	public List<OrdersGoodsBO> getNotEvaluateOrderGoods(String orderId) {
		StringBuilder sql = new StringBuilder();
		StatementParameter params = new StatementParameter();
		sql.append("select og.id,og.file_name as cover_image,og.goods_code code,og.goods_name as name,");
		sql.append("og.goods_id,o.shop_id,og.number,og.is_open_sku,");
		sql.append("og.is_open_member,og.sku_content_name standard,og.price,og.reference_price");
		sql.append(" from orders_goods og");
		sql.append(" inner join orders o on og.order_id=o.id");
		sql.append(" where og.order_id = ? and og.is_delete = ? and og.status <> ?");
		params.setString(orderId);
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(OrderGoodsStatus.ESTIMATE.toIntValue());
		sql.append(" order by og.created_time desc");
		return super.queryForList(sql.toString(), OrdersGoodsBO.class, params);
	}

	@Override
	public int getOrderCountByGoodsId(String goodsId) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select count(*) from orders_goods");
		sql.append(" where goods_id=?");

		StatementParameter param = new StatementParameter();
		param.setString(goodsId);

		return super.queryForInt(sql.toString(), param);
	}
	
	public List<OrdersGoods> getOrderGoodsByOrderId(String orderId) {
		StringBuilder sql = new StringBuilder();
		StatementParameter params = new StatementParameter();
		sql.append("select * from orders_goods where order_id = ?");
		params.setString(orderId);
		return super.queryForList(sql.toString(), OrdersGoods.class, params);
	}

	@Override
	public int countNotEvaluateOrderGoods(String orderId) {
		StringBuilder sql = new StringBuilder();
		StatementParameter params = new StatementParameter();
		
		sql.append("select count(*) from orders_goods where is_delete = ? and status <> ? and order_id =?");
		params.setInt(IsDelete.NO.toIntValue());
		params.setInt(OrderGoodsStatus.ESTIMATE.toIntValue());
		params.setString(orderId);
		
		return queryForInt(sql.toString(), params);
	}

	@Override
	public String getOrderGoodsByOrderNumber(String orderNumber) {
		String sql="select og.id from orders o inner join orders_goods og on o.id=og.order_id where o.order_number =?";
		StatementParameter param=new StatementParameter();
		param.setString(orderNumber);
		return queryForString(sql,param);
	}

	@Override
	public List<RebateInfoBO> getRebateGoods(Date date) {
		StringBuffer sql = new StringBuffer();
		sql.append("select o.pay_time,o.`status`,g.id as goodsId,g.price as goodsPrice,o.pay_money,g.shop_id");
		sql.append(" ,o.user_id,g.`name` as goodsName,og.number,g.is_open_rebate,sc.price as skuPrice,og.is_open_sku");
		sql.append(" from orders_goods og");
		sql.append(" inner join orders o on o.id = og.order_id");
		sql.append(" inner join goods g on g.id = og.goods_id ");
		sql.append(" left join sku_content sc on sc.id = og.sku_content_id");
		sql.append(" where g.is_open_rebate = ?");
		StatementParameter params = new StatementParameter();
		params.setInt(Is.YES.toIntValue());
		if(date != null){
			sql.append(" and o.confirm_time >= ? and o.confirm_time < ?");
			params.setDate(DateUtil.getDateStart(date));
			params.setDate(DateUtil.getDateEnd(date));
		}
		sql.append(" order by o.confirm_time");
		return super.queryForList(sql.toString(),RebateInfoBO.class,params);
	}

	@Override
	public OrdersGoodsBO getOrdersGoods(String ordersGoodsId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select og.is_refund_goods,gr.id 'goods_refund_id',o.shop_id from orders_goods og");
		sql.append(" inner join orders o on o.id = og.order_id");
		sql.append(" left join goods_refund gr on gr.orders_goods_id = og.id and gr.is_delete = ?");
		sql.append(" where og.id = ? and og.is_delete = ?");
		StatementParameter params = new StatementParameter();
		params.setInt(Is.NO.toIntValue());
		params.setString(ordersGoodsId);
		params.setInt(Is.NO.toIntValue());
		return super.query(sql.toString(),OrdersGoodsBO.class,params);
	}

	@Override
	public List<OrdersGoods> getCanRefundGoods(Date confirmDate) {
		StringBuffer sql = new StringBuffer();
		sql.append("select og.* from orders_goods og inner join orders o on og.order_id = o.id");
		sql.append(" where o.status in (?,?) and o.confirm_time <= ? and og.can_refund = ?");
		sql.append(" and og.is_refund_goods = ? and og.is_refund_money = ? and og.is_delete = ?");
		StatementParameter params = new StatementParameter();
		
		params.setInt(OrderStatus.CONFIRM.toIntValue());
		params.setInt(OrderStatus.HASUSE.toIntValue());
		params.setDate(confirmDate);
		params.setInt(Is.YES.toIntValue());
		params.setInt(Is.NO.toIntValue());
		params.setInt(Is.NO.toIntValue());
		params.setInt(Is.NO.toIntValue());
		
		return super.queryForList(sql.toString(),OrdersGoods.class,params);
	}

}
